<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <title>SQL  &middot; sitename</title>
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="HandheldFriendly" content="True">
<meta name="MobileOptimized" content="320">
<meta name="viewport" content="width=device-width, initial-scale=1">


<meta name="description" content="SQL description" />

<meta name="keywords" content="one, two, ">


<meta property="og:title" content="SQL  &middot; sitename ">
<meta property="og:site_name" content="sitename"/>
<meta property="og:url" content="https://lvzongcheng.gitee.io/sql/sql/" />
<meta property="og:locale" content="en-EN">


<meta property="og:type" content="article" />
<meta property="og:description" content="SQL description"/>
<meta property="og:article:published_time" content="2018-08-14T00:00:00Z" />
<meta property="og:article:modified_time" content="2018-08-14T00:00:00Z" />

  
    
<meta property="og:article:tag" content="one">
    
<meta property="og:article:tag" content="two">
    
  

  

<script type="application/ld+json">
  {
    "@context": "http://schema.org",
    "@type": "Article",
    "headline": "SQL",
    "author": {
      "@type": "Person",
      "name": "LvZongCheng"
    },
    "datePublished": "2018-08-14",
    "description": "SQL description",
    "wordCount":  841 
  }
</script>



<link rel="canonical" href="https://lvzongcheng.gitee.io/sql/sql/" />

<link rel="apple-touch-icon-precomposed" sizes="144x144" href="https://lvzongcheng.gitee.io/touch-icon-144-precomposed.png">
<link href="https://lvzongcheng.gitee.io/favicon.png" rel="icon">

<meta name="generator" content="Hugo 0.60.0" />

  <!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->

<link href='https://fonts.googleapis.com/css?family=Merriweather:300%7CRaleway%7COpen+Sans' rel='stylesheet' type='text/css'>
<link rel="stylesheet" href="/css/font-awesome.min.css">
<link rel="stylesheet" href="/css/style.css">
<link rel="stylesheet" href="/css/highlight/default.css">

  
  
	<script>
	  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
	  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
	  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
	  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

	  ga('create', 'Your Google Analytics tracking code', 'auto');
	  ga('send', 'pageview');

	</script>

</head>
<body>
  <main id="main-wrapper" class="container main_wrapper has-sidebar">
    <header id="main-header" class="container main_header">
  <div class="container brand">
  <div class="container title h1-like">
  <a class="baselink" href="https://lvzongcheng.gitee.io">
  LvZongCheng

</a>

</div>

  
<div class="container topline">
  
  淡定 执著 进取 自律


</div>


</div>

  <nav class="container nav primary no-print">
  

<a class="homelink" href="https://lvzongcheng.gitee.io">home</a>


  
<a href="https://lvzongcheng.gitee.io/framework" title="Show list of Frameworks">Frameworks</a>

<a href="https://lvzongcheng.gitee.io/java" title="Show list of Java">Java</a>

<a href="https://lvzongcheng.gitee.io/linux" title="Show list of Linux">Linux</a>

<a href="https://lvzongcheng.gitee.io/redis" title="Show list of Redis">Redis</a>

<a href="https://lvzongcheng.gitee.io/sql" title="Show list of SQL">SQL</a>

<a href="https://lvzongcheng.gitee.io/Spring" title="Show list of Spring">Spring</a>

<a href="https://lvzongcheng.gitee.io/tools" title="Show list of Tools">Tools</a>

<a href="https://lvzongcheng.gitee.io/webpage" title="Show list of WebPage">WebPage</a>

<a href="https://lvzongcheng.gitee.io/information" title="Show list of information">information</a>

<a href="https://lvzongcheng.gitee.io/network" title="Show list of network">network</a>

<a href="https://lvzongcheng.gitee.io/%E5%BE%AE%E6%9C%8D%E5%8A%A1" title="Show list of 微服务">微服务</a>

<a href="https://lvzongcheng.gitee.io/%E6%80%BB%E7%BB%93" title="Show list of 总结">总结</a>

<a href="https://lvzongcheng.gitee.io/%E6%9C%BA%E5%99%A8%E8%87%AA%E5%8A%A8%E5%8C%96" title="Show list of 机器自动化">机器自动化</a>

<a href="https://lvzongcheng.gitee.io/%E6%B1%87%E7%BC%96" title="Show list of 汇编">汇编</a>

<a href="https://lvzongcheng.gitee.io/%E8%AE%B0%E4%BA%8B%E6%9C%AC" title="Show list of 记事本">记事本</a>


</nav>

<div class="container nav secondary no-print">
  
<a id="contact-link-email" class="contact_link" rel="me" aria-label="Email" href="mailto:Lyc19880405@163.com">
  <span class="fa fa-envelope-square"></span></a>



<a id="contact-link-github" class="contact_link" rel="me" aria-label="Github" href="https://github.com/enten/hugo-boilerplate">
  <span class="fa fa-github-square"></span></a>




 


















</div>


  

</header>


<article id="main-content" class="container main_content single">
  <header class="container hat">
  <h1>SQL
</h1>

</header>

  <div class="container content">
  <ol>
<li>
<p>MySQL 索引原理及优化
InnoDB 如何使用 B+ 树存储索引和数据
组合索引在 InnoDB 中是如何存储的
最左前缀匹配原则和最左前缀索引的创建
Explain 命令详解</p>
</li>
<li>
<p>MySQL 锁原理及案例实践
正确理解 InnoDB 中的锁
行锁的加锁原理分析
死锁底层原理分析</p>
</li>
<li>
<p>MySQL 分库分表原理及实践
为什么要分库分表
如何实现分库分表
MyCat 中的分片规则介绍
MyCat 的分库分表配置</p>
</li>
</ol>
<h3 id="sql">标准SQL语句分类</h3>
<ul>
<li>DML 数据操作语言 SELECT、UPDATE、DELETE、INSERT</li>
<li>DDL 数据定义语言 CREATE、 ALTER、DROP、TRUNCATE</li>
<li>DCL 数据控制语言 GRANT、REVOKE</li>
<li>事物控制语句 commit、rollback、savepoint</li>
</ul>
<h6 id="dml">DML语法：</h6>
<p>SELECT 列名称 FROM 表名称<br>
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值<br>
DELETE FROM 表名称 WHERE 列名称 = 值<br>
INSERT INTO table_name (列1, 列2,&hellip;) VALUES (值1, 值2,&hellip;.)</p>
<h6 id="ddl">DDL语法：</h6>
<p>CREATE DATABASE - 创建新数据库<br>
ALTER DATABASE - 修改数据库<br>
CREATE TABLE - 创建新表<br>
ALTER TABLE - 变更（改变）数据库表<br>
DROP TABLE - 删除表<br>
CREATE INDEX - 创建索引（搜索键）<br>
DROP INDEX - 删除索引</p>
<h4 id="sql1">SQL特殊用法</h4>
<p>SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中
SELECT DISTINCT 列名称 FROM 表名称    关键词 DISTINCT 用于返回唯一不同的值。
INSERT INTO table_name (列1, 列2,&hellip;) VALUES  SELECT 列名称 FROM 表名称</p>
<h5 id="heading">公用表达式</h5>
<h6 id="heading1">公用表达式的定义，只包含三部分：</h6>
<ul>
<li>公用表表达式的名字（在WITH之后）</li>
<li>所涉及的列名（可选）</li>
<li>一个SELECT语句(紧跟AS之后)
语法：
WITH 自定义名 AS()   直接使用自定义名的DML语句
理解：WITH 自定义名 AS(SELECT语句) 相当于建立了一个临时表 表名为“ 自定义名”</li>
</ul>
<p>使用CTE，可以增加SQL语句的可维护性，同时，CTE要比表变量的效率高得多。</p>
<h6 id="cte">CTE时应注意如下几点：</h6>
<ol>
<li>CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等）</li>
<li>CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔</li>
<li>如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句
使用的就是数据表或视图了</li>
<li>CTE 可以引用自身，也可以引用在同一WITH 子句中预先定义的CTE。不允许前向引用。</li>
<li>不能在CTE_query_definition 中使用以下子句：
*  COMPUTE 或COMPUTE BY
*  ORDER BY（除非指定了TOP 子句）
*  INTO
*  带有查询提示的OPTION 子句
*  FOR XML
*  FOR BROWSE</li>
<li>如果将CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾</li>
</ol>
<h5 id="-case">条件语句 Case</h5>
<p>Case具有两种格式。简单Case函数和Case搜索函数。</p>
<h6 id="case">简单Case函数</h6>
<p>CASE 字段<br>
WHEN &lsquo;1&rsquo; THEN &lsquo;男&rsquo;  
WHEN &lsquo;2&rsquo; THEN &lsquo;女&rsquo;  
ELSE &lsquo;其他&rsquo; END<br>
（注释：列名，这里是sex，如果放在case后，则显示在屏幕上的列名即为列名sex）</p>
<h6 id="case1">Case搜索函数</h6>
<p>CASE WHEN 字段 = &lsquo;1&rsquo; THEN &lsquo;男&rsquo;  
WHEN 字段 = &lsquo;2&rsquo; THEN &lsquo;女&rsquo;  
ELSE &lsquo;其他&rsquo; END</p>
<hr>
<h4 id="heading2">常用函数</h4>
<p><a href="http://www.w3school.com.cn/sql/sql_orderby.asp">SQL帮助文档</a>  
WHERE<br>
ORDER BY 语句默认按照升序对记录进行排序。<br>
COUNT(<em>) 函数返回表中的记录数<br>
TOP 子句用于规定要返回的记录的数目<br>
AVG(column) 	返回某列的平均值<br>
COUNT(column) 	返回某列的行数（不包括 NULL 值）<br>
COUNT(</em>) 	返回被选行数<br>
FIRST(column) 	返回在指定的域中第一个记录的值<br>
LAST(column) 	返回在指定的域中最后一个记录的值<br>
MAX(column) 	返回某列的最高值<br>
MIN(column) 	返回某列的最低值<br>
SUM(column) 	返回某列的总和<br>
MOD(x,y) 	返回除法操作的余数<br>
NOW() 	返回当前的系统日期</p>
<hr>
<hr>
<h3 id="mysql">mySQL</h3>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">CREATE</span> <span style="color:#66d9ef">TABLE</span> <span style="color:#f92672">`</span>lvzongcheng<span style="color:#f92672">`</span>.<span style="color:#f92672">`</span>stuscore<span style="color:#f92672">`</span>( <span style="color:#f92672">`</span>name<span style="color:#f92672">`</span> VARCHAR(<span style="color:#ae81ff">255</span>), <span style="color:#f92672">`</span>subject<span style="color:#f92672">`</span> VARCHAR(<span style="color:#ae81ff">255</span>), <span style="color:#f92672">`</span>score<span style="color:#f92672">`</span> INT, <span style="color:#f92672">`</span>stuid<span style="color:#f92672">`</span> INT ); 
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">张三</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">数学</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">80</span>,<span style="color:#ae81ff">1</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">张三</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">语文</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">89</span>,<span style="color:#ae81ff">1</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">张三</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">英语</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">70</span>,<span style="color:#ae81ff">1</span>);

<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">李四</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">数学</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">90</span>,<span style="color:#ae81ff">2</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">李四</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">语文</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">70</span>,<span style="color:#ae81ff">2</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">李四</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">英语</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">80</span>,<span style="color:#ae81ff">2</span>);

<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">王五</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">数学</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">65</span>,<span style="color:#ae81ff">3</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">王五</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">语文</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">80</span>,<span style="color:#ae81ff">3</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">王五</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">英语</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">73</span>,<span style="color:#ae81ff">3</span>);

<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">赵六</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">数学</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">98</span>,<span style="color:#ae81ff">4</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">赵六</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">语文</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">88</span>,<span style="color:#ae81ff">4</span>);
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> stuscore VALUE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">赵六</span><span style="color:#e6db74">&#39;</span>,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">英语</span><span style="color:#e6db74">&#39;</span>,<span style="color:#ae81ff">79</span>,<span style="color:#ae81ff">4</span>);

<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> stuscore;

<span style="color:#960050;background-color:#1e0010">计</span><span style="color:#960050;background-color:#1e0010">算</span><span style="color:#960050;background-color:#1e0010">每</span><span style="color:#960050;background-color:#1e0010">个</span><span style="color:#960050;background-color:#1e0010">人</span><span style="color:#960050;background-color:#1e0010">的</span><span style="color:#960050;background-color:#1e0010">总</span><span style="color:#960050;background-color:#1e0010">成</span><span style="color:#960050;background-color:#1e0010">绩</span><span style="color:#960050;background-color:#1e0010">并</span><span style="color:#960050;background-color:#1e0010">排</span><span style="color:#960050;background-color:#1e0010">名</span>
<span style="color:#66d9ef">SELECT</span>  NAME,stuid,SUBJECT,<span style="color:#66d9ef">MAX</span>(score) <span style="color:#66d9ef">AS</span> score <span style="color:#66d9ef">FROM</span> stuscore  <span style="color:#66d9ef">GROUP</span> <span style="color:#66d9ef">BY</span> NAME <span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> stuid <span style="color:#66d9ef">DESC</span>;

<span style="color:#960050;background-color:#1e0010">计</span><span style="color:#960050;background-color:#1e0010">算</span><span style="color:#960050;background-color:#1e0010">每</span><span style="color:#960050;background-color:#1e0010">个</span><span style="color:#960050;background-color:#1e0010">人</span><span style="color:#960050;background-color:#1e0010">单</span><span style="color:#960050;background-color:#1e0010">科</span><span style="color:#960050;background-color:#1e0010">的</span><span style="color:#960050;background-color:#1e0010">最</span><span style="color:#960050;background-color:#1e0010">高</span><span style="color:#960050;background-color:#1e0010">成</span><span style="color:#960050;background-color:#1e0010">绩</span>
<span style="color:#66d9ef">SELECT</span> 
  a1.name,
  a1.stuid,
  a1.subject,
  a1.score <span style="color:#66d9ef">AS</span> score 
<span style="color:#66d9ef">FROM</span>
  stuscore a1,
(<span style="color:#66d9ef">SELECT</span> 
      s.name,
      s.stuid,
      <span style="color:#66d9ef">MAX</span>(s.score) <span style="color:#66d9ef">AS</span> score 
    <span style="color:#66d9ef">FROM</span>
      stuscore s 
    <span style="color:#66d9ef">GROUP</span> <span style="color:#66d9ef">BY</span> s.name) a2
    <span style="color:#66d9ef">WHERE</span> a1.stuid<span style="color:#f92672">=</span>a2.stuid <span style="color:#66d9ef">AND</span> a1.score <span style="color:#f92672">=</span> a2.score;
    
<span style="color:#960050;background-color:#1e0010">列</span><span style="color:#960050;background-color:#1e0010">出</span><span style="color:#960050;background-color:#1e0010">各</span><span style="color:#960050;background-color:#1e0010">门</span><span style="color:#960050;background-color:#1e0010">课</span><span style="color:#960050;background-color:#1e0010">程</span><span style="color:#960050;background-color:#1e0010">成</span><span style="color:#960050;background-color:#1e0010">绩</span><span style="color:#960050;background-color:#1e0010">最</span><span style="color:#960050;background-color:#1e0010">好</span><span style="color:#960050;background-color:#1e0010">的</span><span style="color:#960050;background-color:#1e0010">学</span><span style="color:#960050;background-color:#1e0010">生</span><span style="color:#960050;background-color:#1e0010">（</span><span style="color:#960050;background-color:#1e0010">显</span><span style="color:#960050;background-color:#1e0010">示</span><span style="color:#960050;background-color:#1e0010">：</span><span style="color:#960050;background-color:#1e0010">学</span><span style="color:#960050;background-color:#1e0010">号</span><span style="color:#960050;background-color:#1e0010">、</span><span style="color:#960050;background-color:#1e0010">姓</span><span style="color:#960050;background-color:#1e0010">名</span><span style="color:#960050;background-color:#1e0010">、</span><span style="color:#960050;background-color:#1e0010">课</span><span style="color:#960050;background-color:#1e0010">程</span><span style="color:#960050;background-color:#1e0010">、</span><span style="color:#960050;background-color:#1e0010">成</span><span style="color:#960050;background-color:#1e0010">绩</span><span style="color:#960050;background-color:#1e0010">）</span>
<span style="color:#66d9ef">SELECT</span>  NAME,stuid,SUBJECT,score <span style="color:#66d9ef">FROM</span> stuscore <span style="color:#66d9ef">WHERE</span> score<span style="color:#f92672">=</span>(<span style="color:#66d9ef">SELECT</span> <span style="color:#66d9ef">MAX</span>(score)<span style="color:#66d9ef">AS</span> score <span style="color:#66d9ef">FROM</span> stuscore <span style="color:#66d9ef">WHERE</span> SUBJECT<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">数学</span><span style="color:#e6db74">&#39;</span>)<span style="color:#66d9ef">AND</span> SUBJECT<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">数学</span><span style="color:#e6db74">&#39;</span>
<span style="color:#66d9ef">UNION</span>
<span style="color:#66d9ef">SELECT</span>  NAME,stuid,SUBJECT,score <span style="color:#66d9ef">FROM</span> stuscore <span style="color:#66d9ef">WHERE</span> score<span style="color:#f92672">=</span>(<span style="color:#66d9ef">SELECT</span> <span style="color:#66d9ef">MAX</span>(score)<span style="color:#66d9ef">AS</span> score <span style="color:#66d9ef">FROM</span> stuscore <span style="color:#66d9ef">WHERE</span> SUBJECT<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">语文</span><span style="color:#e6db74">&#39;</span>)<span style="color:#66d9ef">AND</span> SUBJECT<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">语文</span><span style="color:#e6db74">&#39;</span>
<span style="color:#66d9ef">UNION</span>
<span style="color:#66d9ef">SELECT</span>  NAME,stuid,SUBJECT,score <span style="color:#66d9ef">FROM</span> stuscore <span style="color:#66d9ef">WHERE</span> score<span style="color:#f92672">=</span>(<span style="color:#66d9ef">SELECT</span> <span style="color:#66d9ef">MAX</span>(score)<span style="color:#66d9ef">AS</span> score <span style="color:#66d9ef">FROM</span> stuscore <span style="color:#66d9ef">WHERE</span> SUBJECT<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">英语</span><span style="color:#e6db74">&#39;</span>)<span style="color:#66d9ef">AND</span> SUBJECT<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">英语</span><span style="color:#e6db74">&#39;</span>
</code></pre></div><h5 id="heading3">分页</h5>
<p>select * from 表名 limit 开始行,每页数<br>
limit关键字的用法：<br>
LIMIT [offset,] rows<br>
offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。 <br>
示例</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span>  <span style="color:#f92672">*</span> <span style="color:#66d9ef">FROM</span> <span style="color:#960050;background-color:#1e0010">表</span><span style="color:#960050;background-color:#1e0010">名</span> <span style="color:#66d9ef">WHERE</span> <span style="color:#ae81ff">1</span><span style="color:#f92672">=</span><span style="color:#ae81ff">1</span> <span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> id <span style="color:#66d9ef">ASC</span> <span style="color:#66d9ef">LIMIT</span> <span style="color:#ae81ff">0</span>,<span style="color:#ae81ff">2</span>  <span style="color:#f92672">/</span><span style="color:#960050;background-color:#1e0010">降</span><span style="color:#960050;background-color:#1e0010">序</span>
</code></pre></div><h4 id="heading4">查询表字段名，字段类型，注释，长度</h4>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span>
	<span style="color:#66d9ef">COLUMN_NAME</span> <span style="color:#66d9ef">AS</span> <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">列名</span><span style="color:#e6db74">&#39;</span>,
	COLUMN_COMMENT,
	DATA_TYPE <span style="color:#66d9ef">AS</span> <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">字段类型</span><span style="color:#e6db74">&#39;</span>,
	COLUMN_TYPE <span style="color:#66d9ef">AS</span> <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">长度加类型</span><span style="color:#e6db74">&#39;</span>
<span style="color:#66d9ef">FROM</span>
	information_schema.<span style="color:#f92672">`</span>COLUMNS<span style="color:#f92672">`</span>
<span style="color:#66d9ef">WHERE</span>
	<span style="color:#66d9ef">TABLE_NAME</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74"> </span><span style="color:#e6db74">&#39;</span>
<span style="color:#66d9ef">GROUP</span> <span style="color:#66d9ef">BY</span> <span style="color:#66d9ef">COLUMN_NAME</span>
</code></pre></div><h4 id="informationschema">information_schema简介</h4>
<p>information_schema 看作是一个数据库，确切说是信息数据库。其中
保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，
数据库的表，表栏的数据类型与访问权 限等</p>
<h5 id="informationschema1">information_schema数据库表说明</h5>
<ol>
<li>
<p>SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。</p>
</li>
<li>
<p>TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。</p>
</li>
<li>
<p>COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。</p>
</li>
<li>
<p>STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。</p>
</li>
<li>
<p>USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。</p>
</li>
<li>
<p>SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。</p>
</li>
<li>
<p>TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。</p>
</li>
<li>
<p>COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。</p>
</li>
<li>
<p>CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。</p>
</li>
<li>
<p>COLLATIONS表：提供了关于各字符集的对照信息。</p>
</li>
<li>
<p>COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。</p>
</li>
<li>
<p>TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。</p>
</li>
<li>
<p>KEY_COLUMN_USAGE表：描述了具有约束的键列。</p>
</li>
<li>
<p>ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。</p>
</li>
<li>
<p>VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。</p>
</li>
<li>
<p>TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表</p>
</li>
</ol>
<hr>
<hr>
<h3 id="oracle">Oracle</h3>
<h5 id="heading5">分页</h5>
<h6 id="1--order-by">1.  无ORDER BY排序的写法。(效率最高)</h6>
<p>(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！)</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> ROWNUM <span style="color:#66d9ef">AS</span> rowno, t.<span style="color:#f92672">*</span>

          <span style="color:#66d9ef">FROM</span> emp t

         <span style="color:#66d9ef">WHERE</span> hire_date <span style="color:#66d9ef">BETWEEN</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060501</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                             <span style="color:#66d9ef">AND</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060731</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

           <span style="color:#66d9ef">AND</span> ROWNUM <span style="color:#f92672">&lt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">20</span>) table_alias

 <span style="color:#66d9ef">WHERE</span> table_alias.rowno <span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">10</span>;
</code></pre></div><h6 id="2-order-by">2. 有ORDER BY排序的写法。(效率最高)</h6>
<p>&ndash;(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！)</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> tt.<span style="color:#f92672">*</span>, ROWNUM <span style="color:#66d9ef">AS</span> rowno

          <span style="color:#66d9ef">FROM</span> (  <span style="color:#66d9ef">SELECT</span> t.<span style="color:#f92672">*</span>

                    <span style="color:#66d9ef">FROM</span> emp t

                   <span style="color:#66d9ef">WHERE</span> hire_date <span style="color:#66d9ef">BETWEEN</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060501</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                                       <span style="color:#66d9ef">AND</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060731</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                <span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> create_time <span style="color:#66d9ef">DESC</span>, emp_no) tt

         <span style="color:#66d9ef">WHERE</span> ROWNUM <span style="color:#f92672">&lt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">20</span>) table_alias

 <span style="color:#66d9ef">WHERE</span> table_alias.rowno <span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">10</span>;
</code></pre></div><p><strong><code>以下是常用的分页写法(效率低)</code></strong></p>
<h6 id="3-order-by1">3. 无ORDER BY排序的写法。(建议使用方法1代替)</h6>
<p>(此方法随着查询数据量的扩张，速度会越来越慢哦！)</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> ROWNUM <span style="color:#66d9ef">AS</span> rowno, t.<span style="color:#f92672">*</span>

          <span style="color:#66d9ef">FROM</span> k_task t

         <span style="color:#66d9ef">WHERE</span> flight_date <span style="color:#66d9ef">BETWEEN</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060501</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                               <span style="color:#66d9ef">AND</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060731</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)) table_alias

 <span style="color:#66d9ef">WHERE</span> table_alias.rowno <span style="color:#f92672">&lt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">20</span> <span style="color:#66d9ef">AND</span> table_alias.rowno <span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">10</span>;

TABLE_ALIAS.ROWNO  <span style="color:#66d9ef">between</span> <span style="color:#ae81ff">10</span> <span style="color:#66d9ef">and</span> <span style="color:#ae81ff">100</span>;
</code></pre></div><h6 id="4-order-by2">4. 有ORDER BY排序的写法.(建议使用方法2代替)</h6>
<p>(此方法随着查询范围的扩大，速度会越来越慢哦！)</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> tt.<span style="color:#f92672">*</span>, ROWNUM <span style="color:#66d9ef">AS</span> rowno

          <span style="color:#66d9ef">FROM</span> (  <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

                    <span style="color:#66d9ef">FROM</span> k_task t

                   <span style="color:#66d9ef">WHERE</span> flight_date <span style="color:#66d9ef">BETWEEN</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060501</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                                         <span style="color:#66d9ef">AND</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060531</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                <span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> fact_up_time, flight_no) tt) table_alias

 <span style="color:#66d9ef">WHERE</span> table_alias.rowno <span style="color:#66d9ef">BETWEEN</span> <span style="color:#ae81ff">10</span> <span style="color:#66d9ef">AND</span> <span style="color:#ae81ff">20</span>;
</code></pre></div><h6 id="5-order-by">5. 另类语法。(有ORDER BY写法）</h6>
<p>(语法风格与传统的SQL语法不同，不方便阅读与理解，为规范与统一标准，不推荐使用。)</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">WITH</span> partdata <span style="color:#66d9ef">AS</span>

     (

        <span style="color:#66d9ef">SELECT</span> ROWNUM <span style="color:#66d9ef">AS</span> rowno, tt.<span style="color:#f92672">*</span>

          <span style="color:#66d9ef">FROM</span> (  <span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

                    <span style="color:#66d9ef">FROM</span> k_task t

                   <span style="color:#66d9ef">WHERE</span> flight_date <span style="color:#66d9ef">BETWEEN</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060501</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                                         <span style="color:#66d9ef">AND</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060531</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                <span style="color:#66d9ef">ORDER</span> <span style="color:#66d9ef">BY</span> fact_up_time, flight_no) tt

         <span style="color:#66d9ef">WHERE</span> ROWNUM <span style="color:#f92672">&lt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">20</span>)

<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> partdata

 <span style="color:#66d9ef">WHERE</span> rowno <span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">10</span>;
</code></pre></div><h6 id="6--order-by">6. 另类语法 。(无ORDER BY写法）</h6>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">WITH</span> partdata <span style="color:#66d9ef">AS</span>

     (

        <span style="color:#66d9ef">SELECT</span> ROWNUM <span style="color:#66d9ef">AS</span> rowno, t.<span style="color:#f92672">*</span>

          <span style="color:#66d9ef">FROM</span> k_task t

         <span style="color:#66d9ef">WHERE</span> flight_date <span style="color:#66d9ef">BETWEEN</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060501</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

                               <span style="color:#66d9ef">AND</span> TO_DATE (<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">20060531</span><span style="color:#e6db74">&#39;</span>, <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">yyyymmdd</span><span style="color:#e6db74">&#39;</span>)

           <span style="color:#66d9ef">AND</span> ROWNUM <span style="color:#f92672">&lt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">20</span>)

<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> partdata

 <span style="color:#66d9ef">WHERE</span> rowno <span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">10</span>;
</code></pre></div><h5 id="heading6">分析：</h5>
<p>Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。</p>
<p>分页查询格式：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> a.<span style="color:#f92672">*</span>, ROWNUM rn

          <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

                  <span style="color:#66d9ef">FROM</span> <span style="color:#66d9ef">table_name</span>) a

         <span style="color:#66d9ef">WHERE</span> ROWNUM <span style="color:#f92672">&lt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">40</span>)

 <span style="color:#66d9ef">WHERE</span> rn <span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">21</span>
</code></pre></div><p>其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM &lt;= 40和RN &gt;= 21控制分页查询的每页的范围。</p>
<p>上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM &lt;= 40这句上。</p>
<p>选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM &lt;= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM &lt;= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> a.<span style="color:#f92672">*</span>, ROWNUM rn

          <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

                  <span style="color:#66d9ef">FROM</span> <span style="color:#66d9ef">table_name</span>) a)

 <span style="color:#66d9ef">WHERE</span> rn <span style="color:#66d9ef">BETWEEN</span> <span style="color:#ae81ff">21</span> <span style="color:#66d9ef">AND</span> <span style="color:#ae81ff">40</span>
</code></pre></div><p>对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。</p>
<p>这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM &lt;= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。</p>
<p>而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。</p>
<p>上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。</p>
<p>这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。</p>
<p>下面简单讨论一下多表联合的情况。</p>
<p>对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。</p>
<p>因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

  <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> a.<span style="color:#f92672">*</span>, ROWNUM rn

          <span style="color:#66d9ef">FROM</span> (<span style="color:#66d9ef">SELECT</span> <span style="color:#f92672">*</span>

                  <span style="color:#66d9ef">FROM</span> <span style="color:#66d9ef">table_name</span>) a

         <span style="color:#66d9ef">WHERE</span> ROWNUM <span style="color:#f92672">&lt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">40</span>)

 <span style="color:#66d9ef">WHERE</span> rn <span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span> <span style="color:#ae81ff">21</span>
</code></pre></div><h4 id="heading7">查询表字段名，字段类型</h4>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> <span style="color:#66d9ef">column_name</span>, data_type <span style="color:#66d9ef">from</span> all_tab_columns <span style="color:#66d9ef">where</span> <span style="color:#66d9ef">TABLE_NAME</span> <span style="color:#f92672">=</span>
</code></pre></div>
</div>


  
</article>
      <footer id="main-footer" class="container main_footer">
  

  <div class="container nav foot no-print">
  
<a href="https://lvzongcheng.gitee.io/license">license</a>


  <a class="toplink" href="#">back to top</a>

</div>

  <div class="container credits">
  
<div class="container footline">
  
  code with <!-- raw HTML omitted --><!-- raw HTML omitted -->


</div>


  
<div class="container copyright">
  
  (c) 2015 yourname.


</div>


</div>

</footer>

    </main>
    
<script type="text/javascript">
  (function() {
    
    
    if (window.location.hostname == "localhost")
      return;
    var dsq = document.createElement('script'); dsq.async = true; dsq.type = 'text/javascript';
    dsq.src = '//your_disqus_shortname.disqus.com/count.js';
    (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
  })();
</script>



<script src="/js/highlight.pack.js"></script>
<script>hljs.initHighlightingOnLoad();</script>



    
  </body>
</html>

